﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.Services;

using System.Configuration;
using System.Data;

/// <summary>
/// Summary description for WebService
/// </summary>
[WebService(Namespace = "http://tempuri.org/")]
[WebServiceBinding(ConformsTo = WsiProfiles.BasicProfile1_1)]
// To allow this Web Service to be called from script, using ASP.NET AJAX, uncomment the following line. 
// [System.Web.Script.Services.ScriptService]
public class WebService : System.Web.Services.WebService {

    public WebService () {

        //Uncomment the following line if using designed components 
        //InitializeComponent(); 
    }

    [WebMethod]
    public string HelloWorld() {
        return "Hello World";
    }

    [WebMethod]
    public void callEveryMin()
    {
        //It is basically per call
        if (DateTime.Now.ToString("hh:mm tt").ToUpper() == "01:01 PM"
                ||
                DateTime.Now.ToString("hh:mm tt").ToUpper() == "01:02 AM"
                ||
                DateTime.Now.ToString("hh:mm tt").ToUpper() == "01:03 AM"
                ||
                DateTime.Now.ToString("hh:mm tt").ToUpper() == "01:04 AM"
                ||
                DateTime.Now.ToString("hh:mm tt").ToUpper() == "01:05 AM"
                ||
                DateTime.Now.ToString("hh:mm tt").ToUpper() == "09:01 PM"
                ||
                DateTime.Now.ToString("hh:mm tt").ToUpper() == "09:02 AM"
                ||
                DateTime.Now.ToString("hh:mm tt").ToUpper() == "09:03 AM"
                ||
                DateTime.Now.ToString("hh:mm tt").ToUpper() == "09:04 AM"
                ||
                DateTime.Now.ToString("hh:mm tt").ToUpper() == "09:05 AM"
                )
        {
            DatabaseBackup();
        }

        ExecuteFromRemoteServer();
        uploadDataToRemoteServer();
    }
    private void DatabaseBackup()
    {
        #region Database Backup
        try
        {
            string sql = @"
DECLARE @DBName VARCHAR(50)
	DECLARE @path VARCHAR(256) 
	DECLARE @file_Name VARCHAR(256) -- filename for backup 
	DECLARE @fileDate VARCHAR(20) -- used for file name

	SET @path = 'D:\db\'
	
	select @fileDate =  CONVERT(VARCHAR(30),GETDATE(),112) --+'_'+ CONVERT(VARCHAR(30),GETDATE(),114)
	set @fileDate = REPLACE(@fileDate,':','_');
	
	set @DBName='GentleParkShowRoom'
			
	SET @file_Name = @path + @DBName + '_' + @fileDate + '.BAK'
	BACKUP DATABASE @DBName TO DISK = @file_Name 
";

            MSSQL.SQLExec(sql);
        }
        catch (Exception ex)
        {
        }
        #endregion
    }

    private void ExecuteFromRemoteServer()
    {
        string sql = "select * from Auto_SQL where Status=1 and ToWorkStationID=" + ConfigurationManager.AppSettings["WorkStationID"].ToString();
        DataSet ds = MySQLDB.SQLExec(sql);

        foreach (DataRow dr in ds.Tables[0].Rows)
        {
            try
            {
                MSSQL.SQLExec(dr["SQLString"].ToString());

                sql = "Update Auto_SQL set Status=2 ,ExecuteTime='"+DateTime.Now.ToString("yyyy-MM-dd hh:mm tt")+@"' where ID=" + dr["ID"].ToString();

                MySQLDB.SQLExec(sql);
                Sendmail.sendEmail("anamuliut@gmail.com", "Database Update Successfully . ShowRoom-" + ConfigurationManager.AppSettings["ShowRoom"].ToString(),  sql);

            }
            catch (Exception ex)
            {
                Sendmail.sendEmail("anamuliut@gmail.com", "Database Update problem . ShowRoom-" + ConfigurationManager.AppSettings["ShowRoom"].ToString(), ex.Message +"</hr>"+sql);
            }
        }
    }


    private void uploadDataToRemoteServer()
    {
        string sql = "Select * from Auto_SQL where Status=1";
        DataSet ds = MSSQL.SQLExec(sql);
        foreach (DataRow dr in ds.Tables[0].Rows)
        {
            sql = @"
                    INSERT INTO Auto_SQL
                       (SQLString
                       ,Status
                       ,ForWorkStationID
                       ,ToWorkStationID
                       ,FromID
                       ,FromTime
                       ,UploadTime
                       ,ExecuteTime
                       ,ExtraField1
                       ,ExtraField2
                       ,ExtraField3
                       ,ExtraField4
                       ,ExtraField5)
                 VALUES
                       ('" + dr["SQLString"].ToString().Replace("'", "''") + @"'
                       ," + dr["Status"].ToString()+@"
                       ," + dr["ForWorkStationID"].ToString()+@"
                       ," + dr["ToWorkStationID"].ToString()+@"
                       ," + dr["ID"].ToString()+@"
                       ,'" + dr["FromTime"].ToString()+@"'
                       ,'"+DateTime.Now.ToString("yyyy-MM-dd hh:mm tt")+@"'
                       ,'" + dr["ExecuteTime"].ToString()+@"'
                       ,'" + dr["ExtraField1"].ToString()+@"'
                       ,'" + dr["ExtraField2"].ToString()+@"'
                       ,'" + dr["ExtraField3"].ToString()+@"'
                       ,'" + dr["ExtraField4"].ToString()+@"'
                       ,'" + dr["ExtraField5"].ToString() + @"');
                ";
            try
            {
                MySQLDB.SQLExec(sql);
                sql = "update Auto_SQL set Status=2 ,UploadTime='" + DateTime.Now.ToString("yyyy-MM-dd hh:mm tt") + @"' where ID=" + dr["ID"].ToString();
                MSSQL.SQLExec(sql);

                Sendmail.sendEmail("anamuliut@gmail.com", "GP(" + ConfigurationManager.AppSettings["ShowRoom"].ToString() + @")-DataUploading Successfully", sql);
            }
            catch (Exception ex)
            {
                Sendmail.sendEmail("anamuliut@gmail.com", "GP(" + ConfigurationManager.AppSettings["ShowRoom"].ToString() + @")-DataUploading Error", ex.Message + "</hr>" + sql);
            }
        }

    }

    [WebMethod]
    public void callEveryHour()
    {
        //
    }

    [WebMethod]
    public void callEveryDay()
    {
        //
    }
}
